﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Xml;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Text;
using System.Data.OleDb;
using System.Data;

using SimpleText.Framework;
using Yogesh.ExcelXml;

public partial class ImportPage : SimpleTextPage 
{

  protected void Page_Load(object sender, EventArgs e)
  {
		InitPage();

		if (CurrentModel == null)
		{
			Response.Redirect("projects.aspx?msg=noprojectselected");
		}
		if (!IsPostBack)
		{
			//Excel XML files
			string[] xmlFiles = Directory.GetFiles(Server.MapPath(@"~/App_Data/importdata/excel_xml"));
			for (int i = 0; i < xmlFiles.Length; i++)
			{
				xmlFiles[i] = (new FileInfo(xmlFiles[i])).Name;
			}
			ExcelXmlFileSel.DataSource = xmlFiles;
			ExcelXmlFileSel.DataBind();
			ExcelXmlFileSel.Items.Insert(0, new ListItem("- upload new -", ""));
			//Excel files 
			string[] files = Directory.GetFiles(Server.MapPath(@"~/App_Data/importdata/excel_native"));
			for (int i = 0; i < files.Length; i++)
			{
				files[i] = (new FileInfo(files[i])).Name;
			}
			ExcelFileSel.DataSource = files;
			ExcelFileSel.DataBind();
			ExcelFileSel.Items.Insert(0, new ListItem("- upload new -", ""));
		}
	}

	protected void SelectExcelBtnClicked(object sender, EventArgs e)
	{
		SelectFile("Excel");

	}

	protected void SelectExcelXmlBtnClicked(object sender, EventArgs e)
	{
		SelectFile("ExcelXml");
	}

	protected void SelectFile(string type)
	{
		//select/upload sheet
		string fileFullPathAndName = "";
		string relativePathToDir = "";
		if (type == "Excel")
		{
			relativePathToDir = @"~/App_Data/importdata/excel_native/";
			if (ExcelFileSel.Value == "")
			{
				//upload new file
				fileFullPathAndName = Server.MapPath(relativePathToDir + ExcelFile.PostedFile.FileName);
				ExcelFile.PostedFile.SaveAs(fileFullPathAndName);
			}
			else
			{
				fileFullPathAndName = Server.MapPath(relativePathToDir + ExcelFileSel.Value);
			}
		}
		else
		{
			relativePathToDir = @"~/App_Data/importdata/excel_xml/";
			if (ExcelXmlFileSel.Value == "")
			{
				//upload new file
				fileFullPathAndName = Server.MapPath(relativePathToDir + ExcelXmlFile.PostedFile.FileName);
				ExcelXmlFile.PostedFile.SaveAs(fileFullPathAndName);
			}
			else
			{
				fileFullPathAndName = Server.MapPath(relativePathToDir + ExcelXmlFileSel.Value);
			}
		}
		
		FileNameAndPathToImport.Value = fileFullPathAndName;

		//make test-run and present result
		DataTable importDT = null;
		if (type == "Excel")
		{
			importDT = GetDataFromExcelOrTextFile(fileFullPathAndName);
		}
		else
		{
			importDT = GetDataFromExcelXmlFile(fileFullPathAndName);
		}
		/*
		Trace.Write("Total rows", importDT.Rows.Count.ToString());
		Trace.Write("Column 1", importDT.Columns[0].ColumnName);
		Trace.Write("Column 2", importDT.Columns[1].ColumnName);
		Trace.Write("Column 3", importDT.Columns[2].ColumnName);
		*/
		Importer importer = new Importer();
		TextWriter debugWriter = new StringWriter();
		importer.DebugWriter = debugWriter;
		importer.RunInTestMode = true;
		importer.DoImport(CurrentModel, importDT);

		ResultLit.Text += "<pre>" + Server.HtmlEncode(importer.DebugWriter.ToString()) + "</pre>";

		Step1Plh.Visible = false;
		Step2Plh.Visible = true;
		ResultPlh.Visible = false;
		if (type == "Excel")
		{
			ImportExcelPlh.Visible = true;
			ImportExcelXmlPlh.Visible = false;
		}
		else
		{
			ImportExcelPlh.Visible = false;
			ImportExcelXmlPlh.Visible = true;
		}
	}

	protected void ImportExcelXmlBtnClicked(object sender, EventArgs e)
	{
		string fileNameAndPathToImport = FileNameAndPathToImport.Value;
		DataTable importDT = GetDataFromExcelXmlFile(fileNameAndPathToImport);
		DoFinalImport(importDT);
	}
	protected void ImportXmlBtnClicked(object sender, EventArgs e)
	{
		string fileNameAndPathToImport = FileNameAndPathToImport.Value;
		DataTable importDT = GetDataFromExcelOrTextFile(fileNameAndPathToImport);
		DoFinalImport(importDT);
	}

	protected void DoFinalImport(DataTable importDT)
	{
		Importer importer = new Importer();
		TextWriter debugWriter = new StringWriter();
		importer.DebugWriter = debugWriter;
		importer.DoImport(CurrentModel, importDT);

		ResultFinalPlh.Text += "<pre>" + Server.HtmlEncode(importer.DebugWriter.ToString()) + "</pre>";

		SaveModel(CurrentModel, CurrentModel.LoadedFromXmlFilePath);

		Step1Plh.Visible = false;
		Step2Plh.Visible = false;
		ResultPlh.Visible = true;
	}

	protected DataTable GetDataFromExcelXmlFile(string inputFilePath)
	{
		if (inputFilePath.ToLower().IndexOf(".xml") == -1)
		{
			throw new ApplicationException("Invalid file uploaded \"" + inputFilePath + "\". File must be and XML file (with .xml extension)");
		}
		ExcelXmlWorkbook book = null;
		book = ExcelXmlWorkbook.Import(inputFilePath);
		
		if (book == null)
		{
			throw new ApplicationException("Error loading XML file from \"" + inputFilePath + "\"");
		}

		Worksheet sheet = book[0];
		DataTable dt = new DataTable();

		for (int i = 0; i < sheet.ColumnCount; i++)
		{
			dt.Columns.Add(sheet[0][i].Value.ToString());
		}
		for (int i = 1; i < sheet.RowCount; i++)
		{
			object[] values = new object[sheet.ColumnCount];
			for (int k = 0; k < sheet.ColumnCount; k++)
			{
				values[k] = sheet[i][k].Value;
			}
			
			dt.Rows.Add(values);
		}

		return dt;

	}

	protected DataTable GetDataFromExcelOrTextFile(string inputFilePath)
	{
		//read input file
		string connectionString = null;

		if (inputFilePath.IndexOf(".xlsx") != -1)
		{
			connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + inputFilePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
		}
		else if (inputFilePath.IndexOf(".xls") != -1)
		{
			connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + inputFilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
		}
		else if (inputFilePath.IndexOf(".txt") != -1)
		{
			connectionString =
				"Provider=Microsoft.ACE.OLEDB.12.0;" +
				"Data Source=\"" + Path.GetDirectoryName(inputFilePath) + "\\\";" +
				"Extended Properties=\"text;HDR=No;FMT=Delimited\"";
		}
		else if (inputFilePath.IndexOf(".csv") != -1)
		{
			connectionString =
				"Provider=Microsoft.ACE.OLEDB.12.0;" +
				"Data Source=\"" + Path.GetDirectoryName(inputFilePath) + "\\\";" +
				"Extended Properties=\"text;HDR=No;FMT=Delimited\"";
		}
		else
		{
			throw new ApplicationException("Invalid filetype \"" + inputFilePath + "\" (must be .xls, .xlsx, .txt or .csv)");
		}
		DataTable importDT = new DataTable();
		using (OleDbConnection inputDbConn = new OleDbConnection(connectionString))
		{
			inputDbConn.Open();

			string fetchDataSql = null;
			if (inputFilePath.IndexOf(".xlsx") != -1)
			{
				fetchDataSql = "Select * From[" + getSheetName(inputDbConn) + "]";
			}
			else if (inputFilePath.IndexOf(".xls") != -1)
			{
				fetchDataSql = "Select * From[" + getSheetName(inputDbConn) + "]";
			}
			else if (inputFilePath.IndexOf(".txt") != -1)
			{
				fetchDataSql = "Select * From " + Path.GetFileName(inputFilePath);
			}
			else if (inputFilePath.IndexOf(".csv") != -1)
			{
				fetchDataSql = "Select * From " + Path.GetFileName(inputFilePath);
			}
			OleDbCommand inputCmd = new OleDbCommand(fetchDataSql, inputDbConn);
			OleDbDataAdapter inputDbAdpt = new OleDbDataAdapter(inputCmd);
			inputDbAdpt.Fill(importDT);
			inputDbAdpt.Dispose();
			inputCmd.Dispose();
		}

		return importDT;
		
	}

	//SaveModel(CurrentModel, CurrentModel.LoadedFromXmlFilePath);

	protected string getSheetName(OleDbConnection ObjConn)
	{
		string strSheetName = String.Empty;
		try
		{
			System.Data.DataTable dtSheetNames = ObjConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
			if (dtSheetNames.Rows.Count > 0)
			{
				strSheetName = dtSheetNames.Rows[0]["TABLE_NAME"].ToString();
			}
			return strSheetName;
		}
		catch (Exception ex)
		{
			throw new Exception("Failed to get the sheet name", ex);
		}
	}

	protected string GetSubstring(string input, int length)
	{
		if (input.Length <= length)
		{
			return input;
		}
		else
		{
			return input;//input.Substring(0, length) + "...";
		}
	}
}
